package com.awesome.db.util;


import com.awesome.db.bean.ColumnInfoBean;
import com.awesome.db.bean.PrimaryKeyColumnInfoBean;
import com.awesome.db.bean.TableInfoBean;

import java.util.ArrayList;
import java.util.List;

/**
 * Author: JFangZ
 * Email:janezhang1901@163.com
 * Date: 2020/3/18 18:40
 * Description:SQL 语句工具类
 */
public class SqlUtil {
    private static final String TAG = "SqlUtil";

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/3/18 19:44
     * Description:获取建表语句
     *
     * @param tableInfoBean 表对应的实体类
     * @return 自动生成的 sql
     */
    public static String getCreateTableSql(TableInfoBean tableInfoBean) throws IllegalStateException {
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE IF NOT EXISTS ").append(tableInfoBean.getTableName()).append(" (");
        // 主键
        if (tableInfoBean.getPrimaryKeyColumnInfoBeanList().size() == 1) {
            if (tableInfoBean.getPrimaryKeyColumnInfoBeanList().get(0).isAutoIncrement()) {
                sql.append(tableInfoBean.getPrimaryKeyColumnInfoBeanList().get(0).getColumnName())
                        .append(" INTEGER PRIMARY KEY AUTOINCREMENT,");
            } else {
                sql.append(tableInfoBean.getPrimaryKeyColumnInfoBeanList().get(0).getColumnName())
                        .append(" ")
                        .append(tableInfoBean.getPrimaryKeyColumnInfoBeanList().get(0).getType())
                        .append(" PRIMARY KEY,");
            }
        } else {
            // 联合主键
            for (PrimaryKeyColumnInfoBean primaryKeyColumnInfoBean : tableInfoBean.getPrimaryKeyColumnInfoBeanList()) {
                sql.append(primaryKeyColumnInfoBean.getColumnName())
                        .append(" ")
                        .append(primaryKeyColumnInfoBean.getType())
                        .append(",");
            }
        }
        // 其余列
        List<ColumnInfoBean> unionUniqueColumnInfoBeanList = new ArrayList<>();
        for (ColumnInfoBean columnInfoBean : tableInfoBean.getColumnInfoBeanList()) {
            sql.append(columnInfoBean.getColumnName())
                    .append(" ")
                    .append(columnInfoBean.getType());
            if (columnInfoBean.isUnique()) {
                if (columnInfoBean.isUnionUnique()) {
                    // 联合键唯一约束,一会儿加
                    unionUniqueColumnInfoBeanList.add(columnInfoBean);
                } else {
                    sql.append(" UNIQUE");
                }
            }
            if (columnInfoBean.isNotNull()) {
                sql.append(" NOT NULL");
            }
            sql.append(",");
        }
        // Union UNIQUE
        if (unionUniqueColumnInfoBeanList.size() > 0) {
            sql.append("UNIQUE(");
            for (int i = 0; i < unionUniqueColumnInfoBeanList.size(); i++) {
                sql.append(unionUniqueColumnInfoBeanList.get(i).getColumnName());
                if (i != unionUniqueColumnInfoBeanList.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
        } else {
            if (tableInfoBean.getPrimaryKeyColumnInfoBeanList().size() == 1) {
                // 去掉最后一个 ","
                int start = sql.lastIndexOf(",");
                if (start != -1) {
                    sql.delete(start, start + "," .length());
                }
            } else {
                // 联合主键
                sql.append(" PRIMARY KEY (");
                for (PrimaryKeyColumnInfoBean primaryKeyColumnInfoBean : tableInfoBean.getPrimaryKeyColumnInfoBeanList()) {
                    sql.append(primaryKeyColumnInfoBean.getColumnName())
                            .append(",");
                }
                // 去掉最后一个 ","
                int start = sql.lastIndexOf(",");
                if (start != -1) {
                    sql.delete(start, start + "," .length());
                }
                sql.append(")");
            }
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/3/18 19:44
     * Description:获取插入语句
     *
     * @return 自动生成的 sql
     */
    public static String getInsertSql(TableInfoBean tableInfoBean) {
        StringBuilder sql = new StringBuilder();
        List<String> columnNameList = new ArrayList<>();
        List<Object> columnValueList = new ArrayList<>();
        sql.append("INSERT INTO ").append(tableInfoBean.getTableName()).append(" (");

        for (PrimaryKeyColumnInfoBean primaryKeyColumnInfoBean : tableInfoBean.getPrimaryKeyColumnInfoBeanList()) {
            if (primaryKeyColumnInfoBean.isAutoIncrement()) {
                continue;
            }
            // 不是自增长主键才添加
            columnNameList.add(primaryKeyColumnInfoBean.getColumnName());
            columnValueList.add("#{" + primaryKeyColumnInfoBean.getColumnName() + "}");
        }
        // 其他列
        for (ColumnInfoBean columnInfoBean : tableInfoBean.getColumnInfoBeanList()) {
            columnNameList.add(columnInfoBean.getColumnName());
            columnValueList.add("#{" + columnInfoBean.getColumnName() + "}");
        }
        // 拼接列名
        for (String columnName : columnNameList) {
            sql.append(columnName).append(",");
        }
        // 去掉最后一个 ","
        int start = sql.lastIndexOf(",");
        if (start != -1) {
            sql.delete(start, start + "," .length());
        }
        // 拼接值
        sql.append(") VALUES (");
        for (Object columnValue : columnValueList) {
            sql.append(columnValue).append(",");
        }
        // 去掉最后一个 ","
        start = sql.lastIndexOf(",");
        if (start != -1) {
            sql.delete(start, start + "," .length());
        }
        sql.append(")");
        return sql.toString();
    }

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/3/18 19:44
     * Description:获取删除语句
     *
     * @return 自动生成的 sql
     */
    public static String getDeleteByIdSql(TableInfoBean tableInfoBean) {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM ").append(tableInfoBean.getTableName())
                .append(" WHERE ")
                .append(tableInfoBean.getPrimaryKeyColumnInfoBeanList().get(0).getColumnName())
                .append("=#{id}");
        return sql.toString();
    }

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/3/18 19:44
     * Description:获取更新语句
     *
     * @return 自动生成的 sql
     */
    public static String getUpdateByIdSql(TableInfoBean tableInfoBean) {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ").append(tableInfoBean.getTableName()).append(" SET ");

        for (ColumnInfoBean columnInfoBean : tableInfoBean.getColumnInfoBeanList()) {
            sql.append(columnInfoBean.getColumnName())
                    .append("=#{")
                    .append(columnInfoBean.getColumnName())
                    .append("},");
        }
        // 去掉最后一个 ","
        int start = sql.lastIndexOf(",");
        if (start != -1) {
            sql.delete(start, start + "," .length());
        }
        sql.append(" WHERE");
        for (PrimaryKeyColumnInfoBean primaryKeyColumnInfoBean : tableInfoBean.getPrimaryKeyColumnInfoBeanList()) {
            sql.append(" ")
                    .append(primaryKeyColumnInfoBean.getColumnName())
                    .append("=#{")
                    .append(primaryKeyColumnInfoBean.getColumnName())
                    .append("}")
                    .append(" AND");
        }
        // 去掉最后一个 " AND"
        start = sql.lastIndexOf(" AND");
        if (start != -1) {
            sql.delete(start, start + " AND" .length());
        }
        return sql.toString();
    }

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/3/18 19:44
     * Description:获取查询语句
     *
     * @return 自动生成的 sql
     */
    public static String getSelectByIdSql(TableInfoBean tableInfoBean) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ")
                .append(tableInfoBean.getTableName())
                .append(" WHERE ")
                .append(tableInfoBean.getPrimaryKeyColumnInfoBeanList().get(0).getColumnName())
                .append("=#{id}");
        return sql.toString();
    }

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/3/18 19:44
     * Description:获取查询语句
     *
     * @return 自动生成的 sql
     */
    public static String getSelectListSql(TableInfoBean tableInfoBean) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ")
                .append(tableInfoBean.getTableName());
        return sql.toString();
    }

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/4/20 18:31
     * Description:获取根据 id 判断是否存在的 sql
     */
    public static String getExistsByIdSql(TableInfoBean tableInfoBean) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT")
                .append(" COUNT(*) AS count")
                .append(" FROM ")
                .append(tableInfoBean.getTableName())
                .append(" WHERE ")
                .append(tableInfoBean.getPrimaryKeyColumnInfoBeanList().get(0).getColumnName())
                .append("=#{id}");
        return sql.toString();
    }

    /**
     * Author: JFangZ
     * Email:janezhang1901@163.com
     * Date:2020/4/20 18:31
     * Description:删除表的 sql
     */
    public static String getDropTableSql(TableInfoBean tableInfoBean) {
        StringBuilder sql = new StringBuilder();
        sql.append("DROP TABLE IF EXISTS ").append(tableInfoBean.getTableName());
        return sql.toString();
    }
}
